package bo;

import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import java.util.List;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class book {
	private String id;
	private String name;
	private String sex;
	private String username;
	private String userpwd;
	private String mail;
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getUserpwd() {
		return userpwd;
	}
	public void setUserpwd(String userpwd) {
		this.userpwd = userpwd;
	}
	public String getMail() {
		return mail;
	}
	public void setMail(String mail) {
		this.mail = mail;
	}
	public book(String id, String name, String sex, String username, String userpwd, String mail) {
		super();
		this.id = id;
		this.name = name;
		this.sex = sex;
		this.username = username;
		this.userpwd = userpwd;
		this.mail = mail;
	}
	public book() {
		super();
	}
	
	public static void ExportExcel(File file, List<book> list) throws BiffException, IOException, RowsExceededException, WriteException, ClassNotFoundException, SQLException{
	 	file.createNewFile();//创建文件
    	WritableWorkbook wwb=Workbook.createWorkbook(file);
		WritableSheet ws=wwb.createSheet("名片表", 0);
		int row=0;
		Label l1=new Label(0,row,"序号");
		Label l2=new Label(1,row,"姓名");
		Label l3=new Label(2,row,"性别");
		Label l4=new Label(3,row,"账号");
		Label l5=new Label(4,row,"密码");
		Label l6=new Label(5,row,"邮箱");
		ws.addCell(l1);ws.addCell(l2);ws.addCell(l3);ws.addCell(l4);ws.addCell(l5);ws.addCell(l6);
		for(int i=0;i<list.size();i++){
			l1=new Label(0,row,list.get(i).getId());
			l2=new Label(1,row,list.get(i).getName());
			l3=new Label(2,row,list.get(i).getSex());
			l4=new Label(3,row,list.get(i).getUsername()+"");
			l5=new Label(4,row,list.get(i).getUserpwd()+"");
			l6=new Label(5,row,list.get(i).getMail());
			ws.addCell(l1);
			ws.addCell(l2);
			ws.addCell(l3);
			ws.addCell(l4);
			ws.addCell(l5);
			ws.addCell(l6);
		}
		wwb.write();//写入
		wwb.close();//关闭
    }
    //将Excel导入到数据库
    public static void ImportData(File file) throws Exception{
    	Workbook wb=Workbook.getWorkbook(file);
    	Sheet sheet=wb.getSheet(0);
    	int row=sheet.getRows();
    	int col=sheet.getColumns();
    	BookDao bookdao = new BookDao();
    	//从第二行开始，跳过第一行的文字信息
    	for(int i=1;i<row;i++){
    		book card=null;
    		String sno=sheet.getCell(0, i).getContents();
    		String name=sheet.getCell(1,i).getContents();
    		String sex=sheet.getCell(2,i).getContents();
    		String id=sheet.getCell(3,i).getContents();
    		String password=sheet.getCell(4,i).getContents();
    		String email=sheet.getCell(5,i).getContents();
    		card=new book(sno,name,sex,id,password,email);
    		bookdao.add(card);//将名片插入数据库
    	}

}
}
